package com.dldata.devtools30.utils.dbtable.imp;

import com.dldata.devtools30.entity.DevtModelMetadataFieldEntity;
import com.dldata.devtools30.entity.DevtModelMetadataTableEntity;
import com.dldata.devtools30.utils.StringUtil;
import com.dldata.devtools30.utils.dbtable.DbTableManager;
import com.dldata.devtools30.utils.jdbc.JdbcDataAcess;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.text.MessageFormat;
import java.util.*;


/**
 * SQLServer的表工具类
 * @author jueyue
 *
 */
public class DbTableServiceSQLServerJdbcImpl extends DbTableServiceIAbstract {

	private Logger logger = LoggerFactory.getLogger(this.getClass());

	private JdbcDataAcess jdbcDataAcess;

	public DbTableServiceSQLServerJdbcImpl(JdbcDataAcess jdbcDataAcess){
		super(null,jdbcDataAcess);
		this.jdbcDataAcess = jdbcDataAcess;
	}

	public void createNewTable(DevtModelMetadataTableEntity table, List<DevtModelMetadataFieldEntity> formFieldEntities, boolean isdrop) {
		StringBuilder strList = new StringBuilder();
		StringBuilder keyList = new StringBuilder();
		if (isdrop){
			strList.append(" If Exists (SELECT name  FROM sysobjects  WHERE  name = '" + table.getCode() + "' AND type = 'U' ) Drop Table " + table.getCode());
			logger.debug(strList.toString());
			jdbcDataAcess.dbUpdate(strList.toString());
		}
		strList.append(" Create Table " + table.getCode());
		strList.append(" (");
		String tmps="";
		for (int i = 0; i < formFieldEntities.size(); i++) {
			DevtModelMetadataFieldEntity sysCrfFormFieldEntity = formFieldEntities.get(i);
			tmps = getFieldLx_MsSql(sysCrfFormFieldEntity, "new");
			if (sysCrfFormFieldEntity.getPkey()!=null&&sysCrfFormFieldEntity.getPkey()==1){
				strList.append(tmps + " not null ,");
				keyList.append(sysCrfFormFieldEntity.getFieldCode() + ",");
			}
			else{
				strList.append(tmps + " null,");
			}
		}
		if (keyList.length() > 0){
			keyList=keyList.deleteCharAt(keyList.length() - 1);
			String msg1 = "constraint PK_{0} primary key ({1}) ";
			msg1= MessageFormat.format(msg1,new Object[]{table.getCode(),keyList.toString()});
			strList.append(msg1);
		}
		strList.append(") ");
		logger.debug(strList.toString());
		jdbcDataAcess.dbUpdate(strList.toString(), new LinkedList<>());
		return;
	}

	
	public Boolean isExistTable(String tableName) {
		StringBuilder querySql= new StringBuilder();
		querySql.append(" select c.name,cast(isnull(f.[value], '') as nvarchar(100)) as remark ");
		querySql.append(" from sys.objects c ");
		querySql.append(" left join sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1 ");
		querySql.append(" where c.type='u' and c.name=? ");
//		querySql.append(tableName);
//		Object[] args = new Object[1];
//		args[0]=tableName;
		List argsList=new LinkedList<>();
		argsList.add(tableName);

		logger.debug(querySql.toString());
		return jdbcDataAcess.dbsearch(querySql.toString(), argsList).size()>0;
	}

	public Integer queryRecordCount(String tableName) {
		DevtModelMetadataTableEntity crfFormEntity=new DevtModelMetadataTableEntity();
		crfFormEntity.setCode(tableName);
		DevtModelMetadataFieldEntity sysCrfFormFieldEntity=
				queryTableFields(crfFormEntity).stream().sorted(
						new Comparator<DevtModelMetadataFieldEntity>() {
							@Override
							public int compare(DevtModelMetadataFieldEntity o1, DevtModelMetadataFieldEntity o2) {
								if(o1.getPkey()==1){
									Integer intnum1=0,intnum2=0;
									intnum1=-1;
									return intnum1.compareTo(intnum2);
								}
								if(o2.getPkey()==1){
									Integer intnum1=0,intnum2=0;
									intnum2=-1;
									return intnum1.compareTo(intnum2);
								}
								return DbTableManager.FIELDTYPE.valueOfCode(o1.getFieldType()).sort.compareTo(DbTableManager.FIELDTYPE.valueOfCode(o2.getFieldType()).sort);
							}
						}
				).findFirst().orElse(null);
		String fieldcode=sysCrfFormFieldEntity==null?"*":sysCrfFormFieldEntity.getFieldCode();
		StringBuilder querySql= new StringBuilder();
		querySql.append(" select count("+fieldcode+")  as total ");
		querySql.append(" from  ");
		querySql.append(tableName);
		List<HashMap> mapList= jdbcDataAcess.dbsearch(querySql.toString(), new LinkedList<>());
		if(mapList.size()>0&&mapList.get(0).get("total")!=null){
			return Integer.parseInt(mapList.get(0).get("total").toString());
		}
		return 0;
	}

	public void updateTable(DevtModelMetadataTableEntity table, List<DevtModelMetadataFieldEntity> formFieldEntities) {
//		String sql = "select column_name,data_type,column_comment,numeric_precision,numeric_scale,character_maximum_length," +
//				"is_nullable nullable from information_schema.columns where table_name =  '"+table.getCode();

		for (int i = 0; i < formFieldEntities.size(); i++) {
			DevtModelMetadataFieldEntity sysCrfFormFieldEntity = formFieldEntities.get(i);
			String sql=this.updateTableFiled(sysCrfFormFieldEntity,table.getCode());
			jdbcDataAcess.dbUpdate(sql, new LinkedList<>());
		}

	}

	private String updateTableFiled(DevtModelMetadataFieldEntity fi, String t_code)
	{
		StringBuilder strList = new StringBuilder();
		String tmps = getFieldLx_MsSql(fi, "update");
		strList.append("If Exists(Select a.id From syscolumns a inner join sysobjects d on a.id=d.id " +
				" and d.xtype='U'  Where d.name='" + t_code + "' And a.Name='" + fi.getFieldCode() + "')");
		strList.append("  Begin "); //存在就看类型;
		strList.append("    if not Exists (SELECT a.id FROM syscolumns a  left join systypes b on a.xtype=b.xusertype ");
		strList.append("          inner join sysobjects d on a.id=d.id And d.xtype='U' where d.name='" + t_code + "'");
		strList.append("          And a.Name='" + fi.getFieldCode() + "' And B.name='" + tmps + "')");
		strList.append("     Begin ");
		strList.append("      Alter Table " + t_code + " Alter Column" + getFieldLx_MsSql(fi, "new"));
		strList.append("     End ");
		if (tmps .equals("nvarchar") )   //只有nvarchar和小数数字才进来看长度
		{
			strList.append("    Else");  //类型不一样
			strList.append("     Begin ");
			strList.append("      If Not Exists (SELECT a.id FROM syscolumns a  left join systypes b on a.xtype=b.xusertype ");
			strList.append("             inner join sysobjects d on a.id=d.id And d.xtype='U' where d.name='" + t_code + "'");
			strList.append("             And a.Name='" + fi.getFieldCode() + "' And (B.name='nvarchar' or B.name='varchar') ");
			strList.append("             And COLUMNPROPERTY(a.id,a.name,'PRECISION')=" + fi.getTypeLength() + ")");
			strList.append("         Alter Table  " + t_code + " Alter Column " + getFieldLx_MsSql(fi, "new"));
			strList.append("     End ");
		}
		else if (tmps.equals("numeric"))
		{
			strList.append("    Else ");  //类型不一样
			strList.append("     Begin ");
			strList.append("      If Not Exists (SELECT a.id FROM syscolumns a  left join systypes b on a.xtype=b.xusertype ");
			strList.append("             inner join sysobjects d on a.id=d.id And d.xtype='U' where d.name='" + t_code + "'");
			strList.append("             And a.Name='" + fi.getFieldCode() + "' And B.name='numeric'");
			strList.append("             And COLUMNPROPERTY(a.id,a.name,'PRECISION')=" + fi.getTypeLength());
			strList.append("             And isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)=" + fi.getTypeZeroLength() + ")");
			strList.append("          Alter Table  " + t_code + " Alter Column   " + getFieldLx_MsSql(fi, "new"));
			strList.append("     End ");
		}
		strList.append("  End ");
		strList.append("Else ");
		strList.append("  Begin ");
		strList.append("    Alter Table " + t_code + " Add " + getFieldLx_MsSql(fi, "new") + " null ");
		strList.append("  End ");


		logger.debug(strList.toString());
		return strList.toString();
	}

	private String getFieldLx_MsSql(DevtModelMetadataFieldEntity sysCrfFormFieldEntity, String strlx)
	{
		String s = "";
		switch (DbTableManager.FIELDTYPE.valueOfCode(sysCrfFormFieldEntity.getFieldType()))
		{
			case BOOLEAN:
				if (strlx .equals("new") )
					s = messageFormat(" {0} bit ", sysCrfFormFieldEntity.getFieldCode());
				else
					s = "bit";
				break;
			case DATETIME:
			case DATE:
				if (strlx .equals("new"))
					s = messageFormat(" {0} datetime ", sysCrfFormFieldEntity.getFieldCode());
				else
					s = "datetime";
				break;
			case INTEGER:
				if (strlx .equals("new"))
					s = messageFormat(" {0} int ", sysCrfFormFieldEntity.getFieldCode());
				else
					s = "int";
				break;
			case CASH:
			case DECEMEMOL:
				if (strlx .equals("new")){
					s = messageFormat(" {0} numeric({1},{2}) ", sysCrfFormFieldEntity.getFieldCode() , String.valueOf(sysCrfFormFieldEntity.getTypeLength()) , String.valueOf(sysCrfFormFieldEntity.getTypeZeroLength()));
				}else{
					s = "numeric";
				}
				break;
			case STRING:
				if (strlx .equals("new")){
					if (sysCrfFormFieldEntity.getTypeLength() >= 3999)
						s = messageFormat(" {0} nvarchar(max) ", sysCrfFormFieldEntity.getFieldCode());
					else
						s = messageFormat(" {0} nvarchar({1}) ", sysCrfFormFieldEntity.getFieldCode(), String.valueOf(sysCrfFormFieldEntity.getTypeLength()));
				}else
					s = "nvarchar";
				break;
		}
		return s;
	}

	private String messageFormat(String msg1, Object... args){
		msg1= MessageFormat.format(msg1,args);
		return msg1;
	}


	@Override
	public String createTableSql(DevtModelMetadataTableEntity table, List<DevtModelMetadataFieldEntity> formFieldEntities) {
		StringBuilder keyList = new StringBuilder();
		StringBuilder strList=new StringBuilder();
		strList.append(" Create Table " + table.getCode());
		strList.append(" (");
		strList.append("\n");
		String tmps="";
		for (int i = 0; i < formFieldEntities.size(); i++) {
			strList.append("   ");
			DevtModelMetadataFieldEntity sysCrfFormFieldEntity = formFieldEntities.get(i);
			tmps = getFieldLx_MsSql(sysCrfFormFieldEntity, "new");
			if (sysCrfFormFieldEntity.getPkey()!=null&&sysCrfFormFieldEntity.getPkey()==1){
				strList.append(tmps + " not null ,");
				keyList.append(sysCrfFormFieldEntity.getFieldCode() + ",");
			}
			else{
				strList.append(tmps + " null,");
			}
			strList.append("\n");
		}
		if (keyList.length() > 0){
			strList.append("   ");
			keyList=keyList.deleteCharAt(keyList.length() - 1);
			String msg1 = "constraint PK_{0} primary key ({1}) ";
			msg1= MessageFormat.format(msg1,new Object[]{table.getCode(),keyList.toString()});
			strList.append(msg1);
			strList.append("\n");
		}
		strList.append(") ");
		return strList.toString();
	}

	@Override
	public String updateTableSql(DevtModelMetadataTableEntity table, List<DevtModelMetadataFieldEntity> formFieldEntities) {
		StringBuilder strList=new StringBuilder();
		for (int i = 0; i < formFieldEntities.size(); i++) {
			DevtModelMetadataFieldEntity sysCrfFormFieldEntity = formFieldEntities.get(i);
			String sql=this.updateTableFiled(sysCrfFormFieldEntity,table.getCode());
			if(!StringUtil.isEmptyOrLength0(sql)){
				strList.append(sql);
				strList.append(";\r\n");
				strList.append("\r\n");
			}
		}
		return strList.toString();
	}
}
